﻿#region USING SYSTEM
using YunSpace.Core;
using System.Linq;
using MySql.Data.MySqlClient;
using YunSpace.Data;
using System;
using YunSpace.Core.Excel;
using System.Data;
using System.Reflection;
using System.Data.Common;
using YunSpace.Domain.Entity.OrderFlowManage;
using YunSpace.Domain.Entity.BusinessExportManage;
using System.Collections.Generic;
#endregion
/// <summary>
/// 导出
/// </summary>
namespace YunSpace.Application.BusinessExport
{
    public class BusinessExportApp
    {
        /// <summary>
        /// 有效需求的导出Excel
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="type"></param>
        /// <param name="environment"></param>
        /// <returns></returns>
        //public bool GetDemandToExcelData(string queryJson, string type, string environment = "1")
        //{

        //    var queryParam = queryJson.ToJObject();
        //    var currentUser = OperatorProvider.Provider.GetCurrent();
        //    using (var db = new RepositoryBase())
        //    {
        //        // 订单表(Site_Client_Order)的数据来源于需求表(Site_Clients_ProjectByDemand), 如果不一一配置那就说明数据删除过,不用处理。
        //        var sqlStr = $@"SELECT sco.F_Id, 
        //                        sco.F_WorkFormId,
        //                  IFNULL(su.F_RealName,'') as 'F_FlowUserName',
        //                  sco.F_CreatorTime, 
        //                  IFNULL(scp.F_ActivityTheme, '') as F_ActivityTheme,
        //                        (SELECT group_concat(F_ItemName) FROM Sys_ItemsDetail  WHERE F_ItemId ='04ded926-33b1-4705-9070-41ff554acfd8' AND FIND_IN_SET(F_ItemCode,scp.F_SiteType))  as F_SiteType,
        //                  IFNULL(scp.F_DemandCash, '') as F_DemandCash,
        //                  scp.F_ActivityStartDate,
        //                  scp.F_ActivityEndDate,
        //                  (SELECT group_concat(sys.F_FullName) as F_FullName   FROM Sys_Area  sys  WHERE FIND_IN_SET (sys.F_Id,scp.F_ActivityCityId) )as  F_ActivityCityId, 
        //                  IFNULL(scp.F_SiteArea,'') as F_SiteArea, 
        //                  IFNULL(scp.F_Mating,'') as F_Mating,
        //                  IFNULL(scp.F_Note,'') as F_Note, 
        //                  scp.F_State,
        //                        CASE F_Source WHEN 0 THEN '400电话' WHEN 1 THEN '销售自拓' WHEN 2 THEN '网络订单'  END  as F_Source,
        //                  IFNULL(atype.F_ActivityTypeName,'') as F_ActivityTypeName, 
        //                        IFNULL(scpc.F_Contact,'') as F_Contact, 
        //                  IFNULL(sc.F_ClientName,'') as F_ClientName 
        //                    FROM Site_Client_Order sco
        //                    INNER JOIN Site_Clients_ProjectByDemand scp ON sco.F_DemandId = scp.F_Id
        //                    LEFT JOIN Sys_User su ON sco.F_FlowUserId = su.F_Id
        //                    LEFT JOIN (select sid.F_ItemCode as 'F_ActivityType', sid.F_ItemName as 'F_ActivityTypeName' from Sys_Items si INNER JOIN Sys_ItemsDetail sid ON si.F_Id = sid.F_ItemId AND si.F_EnCode='ActivityType') atype ON scp.F_ActivityType = atype.F_ActivityType
        //                    LEFT JOIN Site_Clients_ContactMapping sccm ON sco.F_DemandId = sccm.F_DemandId
        //                    LEFT JOIN Site_Clients_ProjectByContact scpc ON sccm.F_ContactId = scpc.F_Id
        //                    LEFT JOIN Site_Clients sc ON scpc.F_ClientId = sc.F_Id
        //                    WHERE sco.F_State=1 AND scp.F_State=1 AND sco.F_OrderEnvironment = { environment }";
        //        DbParameter[] parameter =
        //       {
        //            new MySqlParameter("@F_ActivityCityId", queryParam["F_ActivityCityId"]),
        //            new MySqlParameter("@F_ActivityType", queryParam["F_ActivityType"]),
        //            new MySqlParameter("@F_DemandCash", queryParam["F_DemandCash"]),
        //            new MySqlParameter("@F_ClientName", queryParam["F_ClientName"]),
        //            new MySqlParameter("@F_ActivityStartDate", queryParam["F_ActivityStartDate"]),
        //            new MySqlParameter("@F_CreatorTime", queryParam["F_CreatorTime"]),
        //            new MySqlParameter("@F_Source", queryParam["F_Source"]),
        //            new MySqlParameter("@F_State", queryParam["F_State"]),
        //        };
        //        sqlStr = string.Format(sqlStr,
        //            queryParam["F_ActivityCityId"].IsEmpty() ? "" : " and a.F_ActivityCityId=@F_ActivityCityId",
        //            queryParam["F_ActivityType"].IsEmpty() ? "" : " and a.F_ActivityType=@F_ActivityType",
        //            queryParam["F_DemandCash"].IsEmpty() ? "" : " and a.F_DemandCash>=@F_DemandCash",
        //            queryParam["F_ClientName"].IsEmpty() ? "" : " and c.F_Id=@F_ClientName",
        //            queryParam["F_ActivityStartDate"].IsEmpty() ? "" : " and a.F_ActivityStartDate>=@F_ActivityStartDate",
        //            queryParam["F_CreatorTime"].IsEmpty() ? "" : " and a.F_CreatorTime>=@F_CreatorTime",
        //            queryParam["F_Source"].IsEmpty() ? "" : queryParam["F_Source"].ToString() == "-1" ? " and a.F_Source in(0,2)" : " and a.F_Source=@F_Source",
        //            queryParam["F_State"].IsEmpty() ? "" : queryParam["F_State"].ToString() == "-1" ? "" : " and a.F_State=@F_State"
        //            );

        //        var result = db.FindList<DemandToExcelEntity>(sqlStr);
        //        int resultCount = result.Count();

        //        DataTable dt = new DataTable(typeof(DemandToExcelEntity).Name);

        //        dt.Columns.Add("需求编号");
        //        dt.Columns.Add("F_WorkFormId");
        //        dt.Columns.Add("销售人员");
        //        dt.Columns.Add("创建时间");

        //        dt.Columns.Add("活动主题");
        //        dt.Columns.Add("活动类型");
        //        dt.Columns.Add("预算金额");
        //        dt.Columns.Add("活动开始时间");
        //        dt.Columns.Add("活动结束时间");
        //        dt.Columns.Add("活动城市");
        //        dt.Columns.Add("场地类型");
        //        dt.Columns.Add("场地面积");
        //        dt.Columns.Add("层高");
        //        dt.Columns.Add("配套服务");
        //        dt.Columns.Add("备注");
        //        dt.Columns.Add("联系人");
        //        dt.Columns.Add("客户名称");
        //        dt.Columns.Add("状态");
        //        dt.Columns.Add("来源");


        //        dt.Rows.Add("测试");

        //        PropertyInfo[] props = typeof(DemandToExcelEntity).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        //        for (int i = 0; i < resultCount; i++)
        //        {
        //            var item = result[i];

        //            var values = new object[props.Length];
        //            for (int j = 0; j < props.Length; j++)
        //            {
        //                values[j] = props[j].GetValue(item, null);
        //            }
        //            dt.Rows.Add(values);
        //        }

        //        NPOIExcel nPOIExcel = new NPOIExcel();


        //        string title = "有效需求数据" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";

        //        nPOIExcel.ExportWebExcel(dt, title);

        //        return false;
        //    }

        //    }


        /// <summary>
        /// 需求草稿中的导出Excel
        /// </summary>
        /// <param name = "queryJson" ></ param >
        /// < param name="type"></param>
        /// <param name = "environment" ></ param >
        /// < returns ></ returns >
        public bool GetDemandToExcelData(string queryJson)
        {

            var queryParam = queryJson.ToJObject();
            var currentUser = OperatorProvider.Provider.GetCurrent();
            List<DemandToExcelEntity> result;

            using (var db = new RepositoryBase())
            {
                string strSql = @"select 
                                                a.F_Id,
                                                f.F_Id contactId,
                                                b.F_Id F_ProjectId,
                                                d.F_Id F_OrderId,
                                                scb.F_BrandId,
                                                e.F_RealName   F_FlowUserName,
                                                CASE F_Source WHEN 0 THEN '400电话' WHEN 1 THEN '销售自拓' WHEN 2 THEN '网络订单'  END  as F_Source,
                                                c.F_Id F_ClientId,
                                                br.F_BrandName,
                                                c.F_ClientName,
                                                b.F_ProjectName,
                                                a.F_ActivityTheme,
                                                atype.F_ActivityTypeName,
                                                a.F_DemandCash,
                                                DATE_FORMAT(a.F_BuildDate,'%Y-%m-%d') F_BuildDate,
                                                DATE_FORMAT(a.F_ActivityStartDate,'%Y-%m-%d') F_ActivityStartDate,
                                                DATE_FORMAT(a.F_ActivityEndDate,'%Y-%m-%d') F_ActivityEndDate,
                                                (SELECT group_concat(sys.F_FullName) as F_FullName   FROM Sys_Area  sys  WHERE FIND_IN_SET (sys.F_Id,a.F_ActivityCityId) )as  F_ActivityCityId, 
                                                (SELECT group_concat(F_ItemName) FROM Sys_ItemsDetail  WHERE F_ItemId ='04ded926-33b1-4705-9070-41ff554acfd8' AND FIND_IN_SET(F_ItemCode,a.F_SiteType))  as F_SiteType,
                                                a.F_SiteArea,
                                                a.F_Height,
                                                CASE a.F_State WHEN 0 THEN '待分配' WHEN 1 THEN '已分配' WHEN 2 THEN '无效订单'  END  as F_State,
                                                e.F_RealName,
                                                case when d.F_OrderState is null then -1 when d.F_OrderState is not null then d.F_OrderState end F_OrderStateName,
							                    case when d.F_OrderState is null then -1 when d.F_OrderState is not null then d.F_OrderState end F_OrderState,
                                                a.F_CreatorTime,
                                                a.F_PersionNumber,
                                                a.F_Note,
                                                a.F_CloseReason,
                                                a.F_Referral,
                                                ( SELECT  F_ItemName  FROM Sys_ItemsDetail WHERE  F_ItemId = '0bd0d3ab-1ae2-448e-9246-674edaa2c19c' AND F_ItemCode=a.F_IndustryId )  F_IndustryName,
                                                f.F_Contact,
                                                case  a.F_CloseReason when 1 then '客户不提供活动详细信息'
                                                 when 2 then '联系不到客户'
                                                 when 3 then '预算太低'
                                                 when 4 then '客户选择其他场地'
                                                 when 5 then '无场地推荐'
                                                 when 6 then '比稿失败'
                                                 when 7 then '客户跳单'
                                                 when 8 then '活动取消'
                                                 when 9 then '撞单'
                                                 when 10 then '其他'
                                                 when 11 then '重复数据' else '未填写' end as F_CloseReasonState
                                from Site_Clients_ProjectByDemand a
                                inner join Site_Clients c on a.F_ClientId=c.F_Id
                                 LEFT JOIN (select sid.F_ItemCode as 'F_ActivityType', sid.F_ItemName as 'F_ActivityTypeName' from Sys_Items si INNER JOIN Sys_ItemsDetail sid ON si.F_Id = sid.F_ItemId AND si.F_EnCode='ActivityType') atype ON a.F_ActivityType = atype.F_ActivityType
                      
                                left join Site_Clients_AllProjectsMapping sca on sca.F_DemandId=a.F_Id
                                left join Site_Clients_AllProjects b on b.F_Id=sca.F_ProjectId                                                              
                                left join Site_Clients_ContactMapping scc on scc.F_DemandId=a.F_Id
                                left join Site_Clients_ProjectByContact f on f.F_Id=scc.F_ContactId 
                                left join Site_Clients_BrandsMapping scb on scb.F_DemandId=a.F_Id 
                                left join Site_Clients_Brands br on br.F_Id=scb.F_BrandId
                                left join Site_Client_Order d on d.F_DemandId=a.F_Id
                                left join Sys_User e on a.F_CreatorUserId=e.F_Id where 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} order by a.F_CreatorTime desc,d.F_OrderState desc";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ActivityCityId", queryParam["F_ActivityCityId"]),
                    new MySqlParameter("@F_ActivityType", queryParam["F_ActivityType"]),
                    new MySqlParameter("@F_DemandCash", queryParam["F_DemandCash"]),
                    new MySqlParameter("@F_ClientName", queryParam["F_ClientName"]),
                    new MySqlParameter("@F_ActivityStartDate", queryParam["F_ActivityStartDate"]),
                    new MySqlParameter("@F_CreatorTime", queryParam["F_CreatorTime"]+" 00:00:00"),
                    new MySqlParameter("@F_Source", queryParam["F_Source"]),
                    new MySqlParameter("@F_State", queryParam["F_State"]),
                    new MySqlParameter("@F_CreatorTime2", queryParam["F_CreatorTime2"]+" 23:59:59"),

                };
                strSql = string.Format(strSql,
                    queryParam["F_ActivityCityId"].IsEmpty() ? "" : " and a.F_ActivityCityId=@F_ActivityCityId",
                    queryParam["F_ActivityType"].IsEmpty() ? "" : " and a.F_ActivityType=@F_ActivityType",
                    queryParam["F_DemandCash"].IsEmpty() ? "" : " and a.F_DemandCash>=@F_DemandCash",
                    queryParam["F_ClientName"].IsEmpty() ? "" : " and c.F_Id=@F_ClientName",
                    queryParam["F_ActivityStartDate"].IsEmpty() ? "" : " and a.F_ActivityStartDate>=@F_ActivityStartDate",
                    queryParam["F_CreatorTime"].IsEmpty() ? "" : " and a.F_CreatorTime>=@F_CreatorTime",
                    queryParam["F_Source"].IsEmpty() ? "" : queryParam["F_Source"].ToString() == "-1" ? " and a.F_Source in(0,2)" : " and a.F_Source=@F_Source",
                    queryParam["F_State"].IsEmpty() ? "" : queryParam["F_State"].ToString() == "-1" ? "" : " and a.F_State=@F_State",
                    queryParam["F_CreatorTime2"].IsEmpty() ? "" : " and a.F_CreatorTime<=@F_CreatorTime2"

                    );
                try
                {
                    result = db.FindList<DemandToExcelEntity>(strSql, parameter);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }

            int resultCount = result.Count();
            DataTable dt = new DataTable(typeof(DemandToExcelEntity).Name);

            dt.Columns.Add("需求编号");
            dt.Columns.Add("销售人员");
            dt.Columns.Add("创建时间");
            dt.Columns.Add("活动主题");
            dt.Columns.Add("活动类型");

            dt.Columns.Add("预算金额");
            dt.Columns.Add("活动开始时间");
            dt.Columns.Add("活动结束时间");
            dt.Columns.Add("活动城市");
            dt.Columns.Add("行业类型");

            dt.Columns.Add("场地类型");
            dt.Columns.Add("场地面积");
            dt.Columns.Add("活动人数");
            dt.Columns.Add("层高");
            dt.Columns.Add("配套服务");

            dt.Columns.Add("备注");
            dt.Columns.Add("联系人");
            dt.Columns.Add("客户名称");
            dt.Columns.Add("订单状态");
            dt.Columns.Add("需求来源");

            dt.Columns.Add("关闭理由");
            dt.Columns.Add("SEO来源");

            PropertyInfo[] props = typeof(DemandToExcelEntity).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            for (int i = 0; i < resultCount; i++)
            {
                var item = result[i];

                var values = new object[props.Length];
                for (int j = 0; j < props.Length; j++)
                {
                    values[j] = props[j].GetValue(item, null);
                }
                dt.Rows.Add(values);
            }

            NPOIExcel nPOIExcel = new NPOIExcel();
            string title = "需求数据" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";

            nPOIExcel.ExportWebExcel(dt, title);

            return false;
        }



    }
}
